package com.realshare.bby.service.integral;

import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * 整理数据
 * author leisure
 * email 15075107120@139.com
 * create 2018-10-29 下午 4:37
 * modify 2018-10-29 下午 4:37
 **/
@Service
public class TidyIntegralData {
    private Logger log = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private SQLManager sqlManager;

    /**
     * 借助于处理状态字段（handler_status）整理数据
     * 1、保留给分/扣分最多的记录
     * 2、根据唯一标记查询以及更新具体的身份证号
     * 3、更新用户组身份证号和姓名
     * 4、更新程序处理状态为已处理
     *
     * @return
     */
    String tidyIntegralData() {
        try {
            log.info("开始处理赠送积分后续整理工作");

            //更新每个规则的最后计算的数据时间，必须在去重之前更新
            String sql = "update RULE_CONFIGS t1 set LAST_DATA_TIME=" +
                    "(select DATA_TIME from (select max(DATA_TIME) DATA_TIME,RULE_CONFIG_ID " +
                    "from RULE_INTEGRAL_TASK GROUP BY RULE_CONFIG_ID) t where RULE_CONFIG_ID=t1.id)" +
                    "where t1.ID in (select RULE_CONFIG_ID from RULE_INTEGRAL_TASK GROUP BY RULE_CONFIG_ID)";
            log.info("更新每个规则的最后计算的数据时间" );
            sqlManager.executeUpdate(new SQLReady(sql));

            log.info("开始根据唯一标记查询更新RULE_INTEGRAL_TASK中具体的身份证号");
            //根据个人证件（身份证、军官证）更新个人证件号
            sql = "UPDATE RULE_INTEGRAL_TASK t1 INNER JOIN (" +
                    "SELECT T1.ID_NO,T2.IDENT_NO FROM RULE_INTEGRAL_TASK T1 " +
                    "LEFT JOIN m_ci_customer T2 ON T2.IDENT_NO=T1.ID_NO " +
                    "WHERE T1.HANDLER_STATUS=0 AND T1.IDENTITY_CODE='0' " +
                    "AND length(T2.IDENT_NO)> 0 GROUP BY T1.ID_NO,T2.IDENT_NO" +
                    ") t2 ON T1.ID_NO=T2.ID_NO SET t1.IDENTITY_CODE=t2.IDENT_NO";
            log.info("根据个人证件（身份证、军官证）更新RULE_INTEGRAL_TASK中个人证件号" );
            sqlManager.executeUpdate(new SQLReady(sql));

            //根据客户号更新身份证号
            sql = "UPDATE RULE_INTEGRAL_TASK t1 INNER JOIN (" +
                    "select T1.ID_NO,T2.IDENT_NO from RULE_INTEGRAL_TASK T1 " +
                    "LEFT JOIN m_ci_customer T2 ON T2.CUST_ID=T1.ID_NO " +
                    "where T1.HANDLER_STATUS=0 and T1.IDENTITY_CODE = '0' AND length(T2.IDENT_NO) > 0 " +
                    "GROUP BY T1.ID_NO,T2.IDENT_NO " +
                    ") t2 ON T1.ID_NO=T2.ID_NO SET t1.IDENTITY_CODE=t2.IDENT_NO";
            log.info("根据客户号更新个人证件号" );
            sqlManager.executeUpdate(new SQLReady(sql));

            //根据外部账号（银行卡号、存折号、存单号）更新RULE_INTEGRAL_TASK中个人证件号
            sql = "UPDATE RULE_INTEGRAL_TASK t1 INNER JOIN (" +
                    "select T1.ID_NO,T3.IDENT_NO from RULE_INTEGRAL_TASK T1 " +
                    "LEFT JOIN tf_mdm_ac_rel T2 ON T2.ACCT_NO=T1.ID_NO " +
                    "LEFT JOIN m_ci_customer T3 ON T3.CUST_ID=T2.COD_CUST_NATL_ID " +
                    "where T1.HANDLER_STATUS=0 and T1.IDENTITY_CODE = '0' AND length(T3.IDENT_NO) > 0 " +
                    "GROUP BY T1.ID_NO,T3.IDENT_NO " +
                    ") t2 ON T1.ID_NO=T2.ID_NO SET t1.IDENTITY_CODE=t2.IDENT_NO";
            log.info("根据外部账号（银行卡号、存折号、存单号）更RULE_INTEGRAL_TASK中新个人证件号" );
            sqlManager.executeUpdate(new SQLReady(sql));

            //根据内部账号（流水号）更新个人证件号
            sql = "UPDATE RULE_INTEGRAL_TASK t1 INNER JOIN (" +
                    "select T1.ID_NO,T5.IDENT_NO from RULE_INTEGRAL_TASK T1 " +
                    "LEFT JOIN MC_ACCT_XREF T2 ON T2.COD_ACCT_NO=T1.ID_NO " +
                    "LEFT JOIN TF_MDM_FCRAC_REL T3 ON T3.COD_MC_ACCT_NO=T2.COD_MC_ACCT_NO " +
                    "LEFT JOIN tf_mdm_ac_rel T4 ON T4.ACCT_ID=T3.ACCT_ID " +
                    "LEFT JOIN m_ci_customer T5 ON T5.CUST_ID=T4.COD_CUST_NATL_ID " +
                    "where T1.HANDLER_STATUS=0 and  T1.IDENTITY_CODE = '0' AND length(T5.IDENT_NO) > 0 " +
                    "GROUP BY T1.ID_NO,T5.IDENT_NO " +
                    ") t2 ON T1.ID_NO=T2.ID_NO SET t1.IDENTITY_CODE=t2.IDENT_NO";
            log.info("根据内部账号（流水号）更新RULE_INTEGRAL_TASK中个人证件号" );
            sqlManager.executeUpdate(new SQLReady(sql));

            updateGroupMember();

            //更新程序处理状态
            sql = "update RULE_INTEGRAL_TASK set HANDLER_STATUS = 1 where HANDLER_STATUS = 0";
            sqlManager.executeUpdate(new SQLReady(sql));
        } catch (Exception e) {
            e.printStackTrace();
            return "处理赠送积分后续整理工作时出错，错误信息为：【" + e.getMessage() + "】";
        }
        log.info("结束处理赠送积分后续整理工作");
        return "";
    }

    private void updateGroupMember() {
        log.info("开始根据唯一标记查询更新RULE_GROUP_MEMBERS中具体的身份证号");
        //根据个人证件（身份证、军官证）更新个人证件号
        String sql = "UPDATE RULE_GROUP_MEMBERS t1 INNER JOIN (" +
                "select T1.ID_NO,T2.IDENT_NO,T2.CUST_NAME from RULE_GROUP_MEMBERS T1 " +
                "LEFT JOIN m_ci_customer T2 ON T2.IDENT_NO=T1.ID_NO " +
                "where T1.IDENTITY_CODE is null " +
                "AND T2.IDENT_NO IS NOT NULL " +
                "GROUP BY T1.ID_NO,T2.IDENT_NO,T2.CUST_NAME " +
                ") t2 ON T1.ID_NO=T2.ID_NO SET t1.IDENTITY_CODE=t2.IDENT_NO, t1.NAME=t2.CUST_NAME";
        log.info("根据个人证件（身份证、军官证）更新RULE_GROUP_MEMBERS中个人证件号" );
        sqlManager.executeUpdate(new SQLReady(sql));

        //根据客户号更新身份证号
        sql = "UPDATE RULE_GROUP_MEMBERS t1 INNER JOIN (" +
                "select T1.ID_NO,T2.IDENT_NO,T2.CUST_NAME from RULE_GROUP_MEMBERS T1 " +
                "LEFT JOIN m_ci_customer T2 ON T2.CUST_ID=T1.ID_NO " +
                "where T1.IDENTITY_CODE is null " +
                "AND T2.IDENT_NO IS NOT NULL " +
                "GROUP BY T1.ID_NO,T2.IDENT_NO,T2.CUST_NAME " +
                ") t2 ON T1.ID_NO=T2.ID_NO SET t1.IDENTITY_CODE=t2.IDENT_NO, t1.NAME=t2.CUST_NAME";
        log.info("根据客户号更新个人证件号" );
        sqlManager.executeUpdate(new SQLReady(sql));

        //根据外部账号（银行卡号、存折号、存单号）更新个人证件号
        sql = "UPDATE RULE_GROUP_MEMBERS t1 INNER JOIN (" +
                "select T1.ID_NO,T3.IDENT_NO,T3.CUST_NAME from RULE_GROUP_MEMBERS T1 " +
                "LEFT JOIN tf_mdm_ac_rel T2 ON T2.ACCT_NO=T1.ID_NO " +
                "LEFT JOIN m_ci_customer T3 ON T3.CUST_ID=T2.COD_CUST_NATL_ID " +
                "where T1.IDENTITY_CODE is null " +
                "AND T3.IDENT_NO IS NOT NULL " +
                "GROUP BY T1.ID_NO,T3.IDENT_NO,T3.CUST_NAME " +
                ") t2 ON T1.ID_NO=T2.ID_NO SET t1.IDENTITY_CODE=t2.IDENT_NO, t1.NAME=t2.CUST_NAME";
        log.info("根据外部账号（银行卡号、存折号、存单号）更新RULE_GROUP_MEMBERS中个人证件号" );
        sqlManager.executeUpdate(new SQLReady(sql));

        //根据内部账号（流水号）更新个人证件号
        sql = "UPDATE RULE_GROUP_MEMBERS t1 INNER JOIN (" +
                "select T1.ID_NO,T5.IDENT_NO,T5.CUST_NAME from RULE_GROUP_MEMBERS T1 " +
                "LEFT JOIN MC_ACCT_XREF T2 ON T2.COD_ACCT_NO=T1.ID_NO " +
                "LEFT JOIN TF_MDM_FCRAC_REL T3 ON T3.COD_MC_ACCT_NO=T2.COD_MC_ACCT_NO " +
                "LEFT JOIN tf_mdm_ac_rel T4 ON T4.ACCT_ID=T3.ACCT_ID " +
                "LEFT JOIN m_ci_customer T5 ON T5.CUST_ID=T4.COD_CUST_NATL_ID " +
                "where T1.IDENTITY_CODE is null " +
                "AND T5.IDENT_NO IS NOT NULL " +
                "GROUP BY T1.ID_NO,T5.IDENT_NO,T5.CUST_NAME " +
                ") t2 ON T1.ID_NO=T2.ID_NO SET t1.IDENTITY_CODE=t2.IDENT_NO, t1.NAME=t2.CUST_NAME";
        log.info("根据内部账号（流水号）更新RULE_GROUP_MEMBERS中个人证件号" );
        sqlManager.executeUpdate(new SQLReady(sql));
    }

}
